package com.sansi.pencilbook.util;

import ch.qos.logback.core.hook.ShutdownHook;
import com.sansi.pencilbook.entity.Book;
import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;

import java.io.File;
import java.io.IOException;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.List;
import java.util.concurrent.ExecutionException;

/**
 * 功能说明: Excel工具类
 *
 * @author lsy13
 */
public class ExcelUtil {

  /**
   * 传入xls文件获得工作簿
   * @param xlsFile excel文件
   * @return
   * @throws Exception
   */
  public static Workbook getWorkBook(File xlsFile) throws Exception {
      return Workbook.getWorkbook(xlsFile);
  }


  /**
   * 传入xls文件获得所有工作表
   * @param xlsFile excel文件
   * @return
   * @throws Exception
   */
  public static Sheet[] getSheets(File xlsFile) throws Exception {
    return getWorkBook(xlsFile).getSheets();
  }

  /**
   * 传入工作簿获得所有工作表
   * @param workbook 工作簿
   * @return
   */
  public static Sheet[] getSheets(Workbook workbook) {
    return workbook.getSheets();
  }

  /**
   * 传入xls文件和工作表的编号, 获得单个工作表
   * @param xlsFile xls文件
   * @param i 所查询的工作表编号
   * @return
   * @throws Exception
   */
  public static Sheet getSheet(File xlsFile, int i) throws Exception {
    return getWorkBook(xlsFile).getSheet(i);
  }

  /**
   * 传入工作簿和工作表的编号, 获得单个工作表
   * @param workbook 工作簿
   * @param i 所查询的工作表编号
   * @return
   */
  public static Sheet getSheet(Workbook workbook, int i) {
    return workbook.getSheet(i);
  }

  /**
   * 根据excel文件和工作表的下标获取相应列数的数据
   * @param xlsFile excel文件
   * @param sheetNum 工作表的下标
   * @param col 列
   * @return
   * @throws Exception
   */
  public static String[] getColData(File xlsFile, int sheetNum, int col) throws Exception {
    Sheet[] sheets = getSheets(xlsFile);
    if (sheets.length > sheetNum) {
      Cell[] cells = sheets[sheetNum].getColumn(col);
      String[] strs = new String[cells.length];
      for (int i=0;i<cells.length;i++) {
        strs[i] = cells[i].getContents();
      }
      return strs;
    }else {
      throw new Exception("工作表下标不存在");
    }
  }

  /**
   * 根据excel文件和工作表的下标获取相应行数的数据
   * @param xlsFile excel文件
   * @param sheetNum 工作表的下标
   * @param row 行
   * @return
   * @throws Exception
   */
  public static String[] getRowData(File xlsFile, int sheetNum, int row) throws Exception {
    Sheet[] sheets = getSheets(xlsFile);
    if (sheets.length > sheetNum) {
      Cell[] cells = sheets[sheetNum].getRow(row);
      String[] strs = new String[cells.length];
      for (int i=0;i<cells.length;i++) {
        strs[i] = cells[i].getContents();
      }
      return strs;
    }else {
      throw new Exception("工作表下标不存在");
    }
  }

  public static String[] getRowData(Sheet sheet, int row) throws Exception {
    Cell[] cells = sheet.getRow(row);
    String[] strs = new String[cells.length];
    for (int i=0;i<cells.length;i++) {
      strs[i] = cells[i].getContents();
    }
    return strs;
  }

  public static String[] getColData(Sheet sheet, int col) throws Exception {
    Cell[] cells = sheet.getColumn(col);
    String[] strs = new String[cells.length];
    for (int i=0;i<cells.length;i++) {
      strs[i] = cells[i].getContents();
    }
    return strs;
  }

}
